import express from 'express';
import mysql from 'mysql2/promise';
import cors from 'cors';

const app = express();
const port = 3000;

// 解析 JSON 数据
app.use(express.json());

// 使用 cors 中间件处理跨域请求
app.use(cors());

// 创建数据库连接池
const pool = mysql.createPool({
  host: 'localhost',
  user: 'root',
  password: '123456',
  database: 'kkmail',
});

// 处理登录请求
app.post('/api/login', async (req: express.Request, res: express.Response) => {
  const { phone_number, password } = req.body;

  try {
    const [rows] = await pool.execute<mysql.RowDataPacket[]>(
      'SELECT * FROM users WHERE phone_number = ? AND password = ?',
      [phone_number, password]
    );

    if (rows.length > 0) {
      // 验证成功
      const user = rows[0];
      const userInfo = {
        phone_number: user.phone_number,
        password: user.password,
        user_nick: user.user_nick, // 添加 user_nick
        id: user.id, // 添加用户 ID
        // 可以根据数据库表结构添加更多字段
      };
      console.log('User Info:', userInfo);
      res.json({ success: true, message: '登录成功', userInfo });
    } else {
      // 验证失败
      res.json({ success: false, message: '手机号或密码错误' });
    }
  } catch (error) {
    console.error('数据库查询出错:', error);
    res.status(500).json({ success: false, message: '服务器内部错误' });
  }
});

// 处理注册请求
app.post('/api/register', async (req: express.Request, res: express.Response) => {
  const { phone_number, password, user_nick } = req.body;
  console.log('收到注册请求:', req.body);

  // 检查参数是否为 undefined
  const validPhoneNumber = phone_number || null;
  const validPassword = password || null;
  const validUserNick = user_nick || null;

  try {
    // 检查手机号是否已存在
    const [existingRows] = await pool.execute<mysql.RowDataPacket[]>(
      'SELECT * FROM users WHERE phone_number = ?',
      [validPhoneNumber]
    );

    if (existingRows.length > 0) {
      // 手机号已存在
      res.json({ success: false, message: '该手机号已注册' });
    } else {
      // 插入新用户数据
      const [result] = await pool.execute(
        'INSERT INTO users (phone_number, password, user_nick) VALUES (?, ?, ?)',
        [validPhoneNumber, validPassword, validUserNick]
      );

      const insertId = (result as any).insertId; // 获取插入的用户 ID
      const userInfo = {
        id: insertId,
        phone_number: validPhoneNumber,
        user_nick: validUserNick,
      };

      // 注册成功
      res.json({ success: true, message: '注册成功' });
    }
  } catch (error) {
    console.error('数据库插入出错:', error);
    res.status(500).json({ success: false, message: '服务器内部错误' });
  }
});

// 获取用户的邮件列表，使用 phone_number 作为参数
app.get('/api/get-emails/:phone_number', async (req: express.Request, res: express.Response) => {
  const phone_number = req.params.phone_number;

  try {
    // 先根据手机号查找用户 ID
    const [userRows] = await pool.execute<mysql.RowDataPacket[]>(
      'SELECT id FROM users WHERE phone_number = ?',
      [phone_number]
    );

    if (userRows.length === 0) {
      // return res.json({ success: false, message: '未找到该手机号对应的用户' });
    }

    const user_id = userRows[0].id;

    // 根据用户 ID 查询该用户作为收件人的邮件列表，使用 JOIN 操作连接 emails 表和 users 表
    const [rows] = await pool.execute<mysql.RowDataPacket[]>(
      `SELECT emails.*, senders.user_nick as sender_name 
      FROM emails 
      JOIN users senders ON emails.sender_id = senders.id 
      WHERE emails.recipient_id = ? AND emails.is_deleted = false AND emails.trashbin = false`,
      [user_id]
    );

    res.json({ success: true, emails: rows });
  } catch (error) {
    console.error('数据库查询出错:', error);
    res.status(500).json({ success: false, message: '服务器内部错误' });
  }
});

// 获取用户发送的邮件列表，使用 phone_number 作为参数
app.get('/api/get-sent-emails/:phone_number', async (req: express.Request, res: express.Response) => {
  const phone_number = req.params.phone_number;

  try {
    // 先根据手机号查找用户 ID
    const [userRows] = await pool.execute<mysql.RowDataPacket[]>(
      'SELECT id FROM users WHERE phone_number = ?',
      [phone_number]
    );

    if (userRows.length === 0) {
      // return res.json({ success: false, message: '未找到该手机号对应的用户' });
    }

    const user_id = userRows[0].id;

    // 根据用户 ID 查询该用户作为发件人的邮件列表，使用 JOIN 操作连接 emails 表和 users 表
    const [rows] = await pool.execute<mysql.RowDataPacket[]>(
      `SELECT emails.*, recipients.user_nick as recipient_name 
      FROM emails 
      JOIN users recipients ON emails.recipient_id = recipients.id 
      WHERE emails.sender_id = ? AND emails.is_deleted = false`,
      [user_id]
    );

    res.json({ success: true, emails: rows });
  } catch (error) {
    console.error('数据库查询出错:', error);
    res.status(500).json({ success: false, message: '服务器内部错误' });
  }
});

// 处理发送邮件请求
app.post('/api/send-email', async (req: express.Request, res: express.Response) => {
  const { sender_id, recipient_phone_number, recipient_id, subject, content } = req.body;

  // 检查参数是否为 undefined
  const validSenderId = sender_id || null;
  const validRecipientId = recipient_id || null;
  const validSubject = subject || null;
  const validContent = content || null;

  // 检查邮件内容是否为无意义的数字
  const isTrash = /^\d+$/.test(validContent);

  try {
    // 验证收件人是否存在
    const [recipientRows] = await pool.execute(
      'SELECT id FROM users WHERE phone_number  = ?',
      [recipient_phone_number]
    ) as [any[], any]; // 类型断言

    if (recipientRows.length === 0) {
      // return res.status(400).json({ success: false, message: '收件人不存在' });
    }

    // 插入新邮件数据
    await pool.execute(
      'INSERT INTO emails (sender_id, recipient_id, subject, content, trashbin) VALUES (?, ?, ?, ?, ?)',
      [validSenderId, validRecipientId, validSubject, validContent, isTrash]
    );

    // 发送成功
    res.json({ success: true, message: '邮件发送成功' });
  } catch (error) {
    console.error('数据库插入出错2:', error);
    res.status(500).json({ success: false, message: '服务器内部错误' });
  }
});

// 标记邮件为星标邮件或取消星标
app.post('/api/mark-starred', async (req: express.Request, res: express.Response) => {
  const { email_id, is_starred } = req.body;

  try {
    await pool.execute(
      'UPDATE emails SET is_starred = ? WHERE id = ?',
      [is_starred, email_id]
    );

    res.json({ success: true, message: '标记成功' });
  } catch (error) {
    console.error('数据库更新出错:', error);
    res.status(500).json({ success: false, message: '服务器内部错误' });
  }
});

app.get('/api/get-starred-emails/:user_id', async (req: express.Request, res: express.Response) => {
  const user_id = req.params.user_id;

  try {
    // 根据用户 ID 查询该用户的星标邮件列表
    const [rows] = await pool.execute<mysql.RowDataPacket[]>(
      `SELECT emails.*, senders.user_nick as sender_name 
      FROM emails 
      JOIN users senders ON emails.sender_id = senders.id 
      WHERE emails.recipient_id = ? AND emails.is_starred = true`,
      [user_id]
    );

    res.json({ success: true, emails: rows });
  } catch (error) {
    console.error('数据库查询出错:', error);
    res.status(500).json({ success: false, message: '服务器内部错误' });
  }
});

// 保存邮件的选中状态
app.post('/api/mark-checked', async (req: express.Request, res: express.Response) => {
  const { email_id, is_checked } = req.body;

  try {
    await pool.execute(
      'UPDATE emails SET is_checked = ? WHERE id = ?',
      [is_checked, email_id]
    );

    res.json({ success: true, message: '保存选中状态成功' });
  } catch (error) {
    console.error('数据库更新出错:', error);
    res.status(500).json({ success: false, message: '服务器内部错误' });
  }
});

// 将邮件移动到已删除列表
app.post('/api/add-to-deleted', async (req: express.Request, res: express.Response) => {
  const { emails } = req.body;
  console.log('Received emails:', emails); // 添加日志检查请求参数

  if (!emails || !Array.isArray(emails)) {
    // return res.status(400).json({ success: false, message: '请求参数错误，缺少 emails 数组' });
  }

  const emailIds = emails.map((email: any) => email.id);

  try {
    // 开启事务，使用 pool.query 方法
    await pool.query('START TRANSACTION');

    // 生成占位符
    const placeholders = emailIds.map(() => '?').join(',');
    const sql = `UPDATE emails SET is_deleted = true WHERE id IN (${placeholders})`;

    // 执行更新操作
    await pool.execute(sql, emailIds);

    // 提交事务，使用 pool.query 方法
    await pool.query('COMMIT');

    res.json({ success: true, message: '邮件已移动到已删除列表' });
  } catch (error) {
    try {
      // 回滚事务，使用 pool.query 方法
      await pool.query('ROLLBACK');
    } catch (rollbackError) {
      console.error('回滚事务出错:', rollbackError);
    }
    console.error('数据库更新出错:', error);
    res.status(500).json({ success: false, message: '服务器内部错误' });
  }
});

// 获取用户的已删除邮件列表
app.get('/api/get-deleted-emails/:phone_number', async (req: express.Request, res: express.Response) => {
  const phone_number = req.params.phone_number;

  try {
    // 先根据手机号查找用户 ID
    const [userRows] = await pool.execute<mysql.RowDataPacket[]>(
      'SELECT id FROM users WHERE phone_number = ?',
      [phone_number]
    );

    if (userRows.length === 0) {
      // return res.json({ success: false, message: '未找到该手机号对应的用户' });
    }

    const user_id = userRows[0].id;

    // 根据用户 ID 查询该用户的已删除邮件列表和垃圾邮件列表
    const [rows] = await pool.execute<mysql.RowDataPacket[]>(
      `SELECT emails.*, senders.user_nick as sender_name 
      FROM emails 
      JOIN users senders ON emails.sender_id = senders.id 
      WHERE (emails.recipient_id = ? OR emails.sender_id = ?) AND (emails.is_deleted = true OR emails.trashbin = true)`,
      [user_id, user_id]
    );

    res.json({ success: true, emails: rows });
  } catch (error) {
    console.error('数据库查询出错:', error);
    res.status(500).json({ success: false, message: '服务器内部错误' });
  }
});

// 新增彻底删除邮件的 API
app.post('/api/delete-forever', async (req: express.Request, res: express.Response) => {
  const { emailIds } = req.body;

  if (!emailIds || !Array.isArray(emailIds)) {
    // return res.status(400).json({ success: false, message: '请求参数错误，缺少 emailIds 数组' });
  }

  try {
    // 开启事务
    await pool.query('START TRANSACTION');

    // 生成占位符
    const placeholders = emailIds.map(() => '?').join(',');
    const sql = `DELETE FROM emails WHERE id IN (${placeholders})`;

    // 执行删除操作
    await pool.execute(sql, emailIds);

    // 提交事务
    await pool.query('COMMIT');

    res.json({ success: true, message: '邮件已彻底删除' });
  } catch (error) {
    try {
      // 回滚事务
      await pool.query('ROLLBACK');
    } catch (rollbackError) {
      console.error('回滚事务出错:', rollbackError);
    }
    console.error('数据库删除出错:', error);
    res.status(500).json({ success: false, message: '服务器内部错误' });
  }
});

// 获取笑话列表
app.get('/api/get-jokes', async (req, res) => {
  try {
    const [rows] = await pool.execute<mysql.RowDataPacket[]>(
      'SELECT * FROM jokes'
    );
    res.json({ success: true, jokes: rows });
  } catch (error) {
    console.error('数据库查询出错:', error);
    res.status(500).json({ success: false, message: '服务器内部错误' });
  }
});

// 获取指定笑话的评论列表
app.get('/api/get-comments/:joke_id', async (req: express.Request, res: express.Response) => {
  const joke_id = req.params.joke_id;

  try {
    const [rows] = await pool.execute<mysql.RowDataPacket[]>(
      'SELECT comments.*, users.user_nick FROM comments JOIN users ON comments.user_id = users.id WHERE comments.joke_id = ?',
      [joke_id]
    );

    res.json({ success: true, comments: rows });
  } catch (error) {
    console.error('数据库查询出错:', error);
    res.status(500).json({ success: false, message: '服务器内部错误' });
  }
});

// 获取笑话列表及对应的评论数量
app.get('/api/get-jokes-with-comment-count', async (req, res) => {
  try {
    const [rows] = await pool.execute<mysql.RowDataPacket[]>(
      `SELECT jokes.*, COUNT(comments.id) as comment_count 
      FROM jokes 
      LEFT JOIN comments ON jokes.id = comments.joke_id 
      GROUP BY jokes.id`
    );
    res.json({ success: true, jokes: rows });
  } catch (error) {
    console.error('数据库查询出错:', error);
    res.status(500).json({ success: false, message: '服务器内部错误' });
  }
});

// 添加评论
app.post('/api/add-comment', async (req: express.Request, res: express.Response) => {
  const { joke_id, user_id, user_nick, comment } = req.body;

  try {
    await pool.execute(
      'INSERT INTO comments (joke_id, user_id, nickname, comment, created_at) VALUES (?, ?, ?, ?, NOW())',
      [joke_id, user_id, user_nick, comment]
    );

    res.json({ success: true, message: '评论添加成功' });
  } catch (error) {
    console.error('数据库插入出错:', error);
    res.status(500).json({ success: false, message: '服务器内部错误' });
  }
});

// 更新用户昵称
app.post('/api/update-nickname', async (req: express.Request, res: express.Response) => {
  const { user_id, user_nick } = req.body;

  // 检查参数是否为 undefined
  const validUserId = user_id || null;
  const validUserNick = user_nick || null;

  try {
    // 更新用户昵称
    await pool.execute(
      'UPDATE users SET user_nick = ? WHERE id = ?',
      [validUserNick, validUserId]
    );

    res.json({ success: true, message: '昵称更新成功' });
  } catch (error) {
    console.error('数据库更新出错:', error);
    res.status(500).json({ success: false, message: '服务器内部错误' });
  }
});

// 获取用户的记事列表
app.get('/api/get-notes/:user_id', async (req: express.Request, res: express.Response) => {
  const user_id = req.params.user_id;

  try {
    const [rows] = await pool.execute<mysql.RowDataPacket[]>(
      'SELECT * FROM notes WHERE user_id = ?',
      [user_id]
    );

    res.json({ success: true, notes: rows });
  } catch (error) {
    console.error('数据库查询出错:', error);
    res.status(500).json({ success: false, message: '服务器内部错误' });
  }
});

// 创建新的记事
app.post('/api/create-note', async (req: express.Request, res: express.Response) => {
  const { user_id, title, content } = req.body;

  try {
    await pool.execute(
      'INSERT INTO notes (user_id, title, content, created_at) VALUES (?, ?, ?, NOW())',
      [user_id, title, content]
    );

    res.json({ success: true, message: '记事创建成功' });
  } catch (error) {
    console.error('数据库插入出错:', error);
    res.status(500).json({ success: false, message: '服务器内部错误' });
  }
});

// 更新记事
app.post('/api/update-note', async (req: express.Request, res: express.Response) => {
  const { note_id, title, content } = req.body;

  try {
    await pool.execute(
      'UPDATE notes SET title = ?, content = ? WHERE id = ?',
      [title, content, note_id]
    );

    res.json({ success: true, message: '记事更新成功' });
  } catch (error) {
    console.error('数据库更新出错:', error);
    res.status(500).json({ success: false, message: '服务器内部错误' });
  }
});

// 删除记事
app.post('/api/delete-note', async (req: express.Request, res: express.Response) => {
  const { note_id } = req.body;

  try {
    await pool.execute(
      'DELETE FROM notes WHERE id = ?',
      [note_id]
    );

    res.json({ success: true, message: '记事删除成功' });
  } catch (error) {
    console.error('数据库删除出错:', error);
    res.status(500).json({ success: false, message: '服务器内部错误' });
  }
});

// 处理反馈提交的 API 接口
app.post('/api/submit-feedback', async (req, res) => {
  try {
    const { name, email, message } = req.body;

    // 插入反馈数据到数据库
    const [result] = await pool.execute(
      'INSERT INTO feedbacks (name, email, message) VALUES (?, ?, ?)',
      [name, email, message]
    );

    // 类型断言
    const resultHeader = result as mysql.ResultSetHeader;
    if (resultHeader.affectedRows === 1) {
      res.json({ success: true });
    } else {
      res.json({ success: false, message: '插入数据失败' });
    }
  } catch (error) {
    console.error('保存反馈时出错:', error);
    res.status(500).json({ success: false, message: '服务器内部错误' });
  }
});





// 启动服务器
app.listen(port, () => {
  console.log(`服务器运行在端口 ${port}`);
});